####################################################-*- coding: utf-8 -*-#####
#
# NOTE: This file should be only called once, after it is called, it should
# be renamed or deleted.
#
# This file is used to create database and tables, we do not use module
# 'mysql' to do the installation job, we call mysql directly via os.system.
#
# NOTE: stock.sql should be modified by adding the following 3 lines:
#    set character_set_client='utf8';
#    set character_set_connection='utf8';
#    set character_set_results='utf8';       
#
##############################################################################
import os
import sys
if sys.version_info[0] >= 3:
    import mysql
    import configparser
else:
    print('ERROR: Only applicable for python3')
    exit()
    
CFG_FILE='stock.cfg'
SQL_FILE='stock.sql'
LOCK_FILE='lock'

def install_db():
    """ Install database and tables, db connect info is read from config file. """
    if sys.version_info[0] < 3:
        print('\n~WARNING~ This tool only supports python 3.\n')
        exit()
    
    rootpath = os.path.dirname(os.getcwd() + os.sep + sys.argv[0])
    cfg_file = rootpath + os.sep + CFG_FILE
    sql_file = rootpath + os.sep + SQL_FILE
    lock_file = rootpath + os.sep + LOCK_FILE
    if os.path.exists(cfg_file) is False or os.path.exists(sql_file) is False:
        print('ERROR: No "%s" or "%s" exists under "%s/"'
              %(CFG_FILE, SQL_FILE, rootpath))
        exit()

    if os.path.exists(lock_file):
        print('\n~WARNING~ PLEASE DO NOT RUN ME MORE THAN ONCE!\n')
        exit()

    # Read config file
    config = configparser.RawConfigParser()
    config.read(cfg_file)
    
    host = config.get("mysql", "host")
    port = config.get("mysql", "port")
    user = config.get("mysql", "user")
    passwd = config.get("mysql", "passwd")
    dbname = config.get("mysql", "dbname")
    if host == None or port == None or user == None or \
            passwd == None or dbname == None:
        print('ERROR: Please config "%s" file correctly!'%CFG_FILE)
        exit()
        
    # Connect to database
    res = None
    try:
        res = mysql.connect(host=host, user=user, passwd=passwd, port=int(port))
    except Exception as msg:
        print('ERROR: %s'%msg)        
        print('\nFail to connect database server %s:%s'%(host, port))
        exit()
        
    # Get cursor
    cursor = res.cursor()

    # Check if database exists.
    sql="SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '%s';"%dbname
    cursor.execute(sql)
    if cursor.rowcount != 0:
        warn='\n[WARNING] DATABASE "%s" ALREADY EXISTS, DELETE ORIGINAL ONE?(YES/no):'%dbname
        delete = input(warn)
        if delete.lower() == 'yes':
            print('[INFO] DELETING DATABASE "%s" ...'%dbname)
            cursor.execute('DROP DATABASE `%s`;'%dbname)
            cursor.close()            
        else:
            print('[INFO] DO NOTHING, EXIT.')
            cursor.close()             
            exit()
    
    # Create database and tables
    print('[INFO] CREATING DATABASE "%s" ...'%dbname)
    cmd = 'mysql -h %s -u %s -P %s -p < %s'%(host, user, port, sql_file)
    print('\n%s'%cmd)
    retval = os.system(cmd)
    
    if retval != 0:
        print('[ERROR] FAILED TO CREATE DATABASE, ERRNO: %d'%retval)
        exit()

    print('[INFO] DATABASE CREATED SUCCESSFULLY!\n')
    # Create the lock file
    lock = open(lock_file, 'w')
    lock.write('PLEASE DO NOT DELETE ME!')
    lock.close()


# Run this module as single
if __name__ == "__main__":
    install_db()
